<?php

/**
 * @file IndexController
 * @author garypradoruiz <garypradoruiz@gmail.com>
 * @link mailto:garypradoruiz@gmail.com
 * @copyleft 2012 garypradoruiz
 * @license GPL
 * @version
 */
//VELCRO
class Report_IndexController extends Zend_Controller_Action {

   // Filtrar: Ejecutivo, Creativo, Clientes
   // Filtro de fechas
   // Nro de orden entregadas

   /* Report main all agency */
   public function indexAction() {
      /*
        SELECT *
        FROM orden_job OJ
        INNER JOIN orden O ON OJ.id_orden = O.id
        INNER JOIN orden_user OU ON OU.id_orden = O.id
        INNER JOIN client C ON C.id = OU.id_client
        WHERE OJ.id NOT
        IN (
        SELECT id_job
        FROM orden_job_activity X
        WHERE STATUS = 4
        AND X.register_date > OJ.end_date_client
        )
       */
   }

   public function numOrdenAgenciaAction() {
      /*
       * Este Reporte genera:
        Nº de solicitudes por agencia //URL = http://localhost/capataz/report/index/num-orden-agencia/idgroup/4
        Nº de solicitudes por grupo //URL = http://localhost/capataz/report/index/num-orden-agencia/idagencia/37
        Flujo de pedidos por semana (x agencia y grupo)
        Flujo de pedidos x mes (x agencia y grupo) //URL = http://localhost/capataz/report/index/num-orden-agencia/idgroup/4/filter/mounth/year/2011/mounth/11
        Flujo de pedidos x año (x agencia y grupo) //URL = http://localhost/capataz/report/index/num-orden-agencia/idagencia/37/filter/year/year/2012
       */

      $iddepartament = $this->_getParam('idagencia', NULL); // resultados x agencia
      $id_group = $this->_getParam('idgroup', NULL); // resultados x grupos
      $year = $this->_getParam('year', 'YEAR( now() )'); // si no existe el parametro year(año) se toma en cuenta el año actual
      $month = $this->_getParam('month', 'MONTH( now() )'); // si no existe el parametro month(mes) se toma en cuenta el mes actual
      $desde = $this->_getParam('desde', '0001-01-01');
      $hasta = $this->_getParam('hasta', '0001-01-01');

      //$agencia_group sirve para saber si los resultados devueltos en la consulta seran por agencia o x grupo
      $agencia_group = ($iddepartament == NULL or !is_numeric($iddepartament)) ?
              ($id_group == NULL or !is_numeric($id_group)) ?
                      'error' :
                      'G.idgroup = ' . $id_group  : 'OD.id_departament = ' . $iddepartament;
      

      if ($agencia_group == 'error') { // si no existe idgroup o idagencia
         $this->view->error = 'param_error'; // error de parametros en la url
         echo "error";
         return;
      }

      $filter = $this->_getParam('filter', NULL); // sirve para filtrar la consulta por año, mes o desde_hasta
      $where = array(
          'month' => "AND MONTH(O.create_date) = $month AND YEAR(O.create_date) = $year",
          'year' => "AND YEAR(O.create_date) = $year",
          'desde_hasta' => "AND O.create_date >= '$desde' AND O.create_date <= '$hasta'"
      );

      //echo $where[$accion];

      /* if ($iddepartament == NULL or !is_numeric($iddepartament)) {
        $this->view->error = 'param_error'; // error de parametros en la url
        return;
        } */
      echo $departament_group;
      $ordenDb = new Orden_Model_DbTable_Orden();
      $select = $ordenDb->select()->setIntegrityCheck(false)
              ->from(array('O' => 'orden'), array('title', 'create_date', 'end_date'))
              ->join(array('OD' => 'orden_departament'), 'OD.id_orden = O.id', array())
              ->join(array('D' => 'departament'), 'D.id = OD.id_departament', array('name_departament' => 'D.name'))
              ->join(array('OU' => 'orden_user'), 'OU.id_orden = O.id', array())
              ->join(array('U' => 'user'), 'OU.id_user = U.id', array())
              ->join(array('G' => 'groups'), 'U.idgroup = G.idgroup', array())
              ->join(array('C' => 'client'), 'OU.id_client = C.id', array('name_client' => 'C.name'))
              ->where($agencia_group . ' ' . $where[$filter]);
      //->where('OD.id_departament = ' . $iddepartament . ' ' . $where[$accion]);
      $data = $ordenDb->fetchAll($select)->toArray();
      if (!count($data) > 0) {
         $this->view->error = 'empty';
         return;
      }
      $this->view->count = count($data);
      $this->view->data = $data;
      var_dump($data);
   }

   public function timmingAction() {
      /*
        Este rporte genera:
        Nº de trabajos realizados en fuera de Timming // URL = http://localhost/capataz/report/index/timming/status/dentro-timming
        Nº de trabajos realizados en Timming // URL = http://localhost/capataz/report/index/timming/status/fuera-timming
       */
      $timming = $this->_getParam('status', NULL); // param debe ser fuera-timming o dentro-timming
      $where = array('fuera-timming' => 'OJA.status < 4 AND OJA.register_date > OJ.end_date_client',
          'dentro-timming' => 'OJA.status = 5 AND OJA.register_date <= OJ.end_date_client');
      if (!isset($where[$timming])) {
         $this->view->error = 'param_error'; // error de parametros en la url
         return;
      }
      $ordenDb = new Orden_Model_DbTable_Orden();
      $select = $ordenDb->select()->setIntegrityCheck(false)
              ->from(array('OJ' => 'orden_job'), array('name_job' => 'name', 'fecha_entrega' => 'end_date_client'))
              ->join(array('O' => 'orden'), 'O.id = OJ.id_orden', array('name_orden' => 'title'))
              ->join(array('OU' => 'orden_user'), 'OU.id_orden = O.id', array())
              ->join(array('C' => 'client'), 'C.id = OU.id_client', array('name_client' => 'name'))
              ->join(array('OJA' => 'orden_job_activity'), 'OJA.id_job = OJ.id', array('OJA.message', 'fecha_ultima_accion' => 'MAX(OJA.register_date)', 'count' => 'count(*)')) // count es el numero de veces que se hizo una accion fuera de timming de un trabajo
              ->where($where[$timming])
              ->group('OJA.id_job')
              ->order('OJA.register_date DESC');
      $data = $ordenDb->fetchAll($select)->toArray();
      if (!count($data) > 0) {
         $this->view->error = 'empty';
         return;
      }
      $this->view->count = count($data);
      $this->view->data = $data;
      var_dump($data);
   }

   public function numJobsAction() {
      /*
        Esta accion genera estos tres tipos de reportes:
        Nº de trabajos pedidos // URL = http://localhost/capataz/report/index/num-jobs/status/pedidos
        Nº de trabajos realizados // URL = http://localhost/capataz/report/index/num-jobs/status/terminados
        Nº de trabajos eliminados x Tigo antes de entrega // URL = http://localhost/capataz/report/index/num-jobs/status/eliminados
       */
      $param = $this->_getParam('status');
      $where = array('terminados' => 'AND OJ.status = 5',
          'pedidos' => 'AND OJ.status < 5 and OJ.status <> 4',
          'eliminados' => '');
      if (!isset($where[$param])) {
         $this->view->error = 'param_error';
         return;
      }
      $not_yes = ($param == 'eliminados') ? '' : 'NOT'; // si es vacio muestra los jobs eliminados
      $ordenDb = new Orden_Model_DbTable_Orden();
      $select = $ordenDb->select()->setIntegrityCheck(false)
              ->from(array('OJ' => 'orden_job'), array('name_job' => 'name', 'detail', 'end_date_client' => 'DATE_FORMAT(DATE(end_date_client), \'%d-%m-%Y\')'))
              ->join(array('O' => 'orden'), 'O.id = OJ.id_orden', array('name_orden' => 'title'))
              ->join(array('OU' => 'orden_user'), 'OU.id_orden = O.id', array())
              ->join(array('C' => 'client'), 'C.id = OU.id_client', array('name_client' => 'name'))
              ->where("OJ.id $not_yes IN 
                      (
                       SELECT id_job
                       FROM orden_job_activity X
                       WHERE STATUS = 4
                       AND X.register_date > OJ.end_date_client
                       ) " . $where[$param]);
      $data = $ordenDb->fetchAll($select)->toArray();
      if (!count($data) > 0) {
         $this->view->error = 'empty';
         return;
      }
      $this->view->count = count($data);
      $this->view->data = $data;
      var_dump($data);
   }

   public function numChangesJobsAction() {
      /*
        Este reporte genera:
        Nº de cambios por trabajo // URL = http://localhost/capataz/report/index/num-changes-jobs
       */

      $ordenDb = new Orden_Model_DbTable_Orden();
      $select = $ordenDb->select()->setIntegrityCheck(false)
              ->from(array('OJ' => 'orden_job'), array('name_job' => 'name', 'detail', 'end_date_client' => 'DATE_FORMAT(DATE(end_date_client), \'%d-%m-%Y\')'))
              ->join(array('O' => 'orden'), 'O.id = OJ.id_orden', array('name_orden' => 'title'))
              ->join(array('OU' => 'orden_user'), 'OU.id_orden = O.id', array())
              ->join(array('C' => 'client'), 'C.id = OU.id_client', array('name_client' => 'name'))
              ->join(array('OJA' => 'orden_job_activity'), 'OJA.id_job = OJ.id', array('count' => 'count(OJA.id_job)'))
              ->group('OJA.id_job');
      $data = $ordenDb->fetchAll($select)->toArray();
      if (!count($data) > 0) {
         $this->view->error = 'empty';
         return;
      }
      $this->view->count = count($data);
      $this->view->data = $data;
      var_dump($data);
   }

   public function numChangesResponsibleAction() {
      /*
        Este reporte genera:
        Nº de cambios responsable Agencia // URL = http://localhost/capataz/report/index/num-changes-responsible/responsible/DEPARTAMENT
        Nº de cambios responsable Tigo // URL = http://localhost/capataz/report/index/num-changes-responsible/responsible/CLIENT
       */
      $responsible = $this->_getParam('responsible');
      if ($responsible != 'CLIENT' and $responsible != 'DEPARTAMENT') {
         $this->view->error = 'param_error';
         return;
      }
      $ordenDb = new Orden_Model_DbTable_Orden();
      $select = $ordenDb->select()->setIntegrityCheck(false)
              ->from(array('OJ' => 'orden_job'), array('name_job' => 'name'))
              ->join(array('O' => 'orden'), 'O.id = OJ.id_orden', array('name_orden' => 'title'))
              ->join(array('OU' => 'orden_user'), 'OU.id_orden = O.id', array())
              ->join(array('C' => 'client'), 'C.id = OU.id_client', array('name_client' => 'name'))
              ->join(array('OJA' => 'orden_job_activity'), 'OJA.id_job = OJ.id', array('OJA.id_user'))
              ->where("OJA.id_user IN (select U.id from user U where type = '$responsible')");
      $data = $ordenDb->fetchAll($select)->toArray();
      if (!count($data) > 0) {
         $this->view->error = 'empty';
         return;
      }
      $this->view->count = count($data);
      $this->view->data = $data;
      var_dump($data);
   }

   public function getReportAction() {
      $this->_helper->layout()->disableLayout();
      $this->_helper->viewRenderer->setNoRender();

      $ordenDb = new Orden_Model_DbTable_Orden();

      $select = $ordenDb->select()->setIntegrityCheck(false)
              ->from(array('od' => 'orden_departament'), array('id_departament'))
              ->join(array('o' => 'orden'), 'o.id = od.id_orden', array('nro_ordenes' => 'COUNT(*)'))->where('o.status != 4')
              ->join(array('d' => 'departament'), 'd.id = od.id_departament')
              ->group('od.id_departament');

      $dataOr = $ordenDb->fetchAll($select);
      $jsonData = Zend_Json::encode($dataOr->toArray());
      echo $jsonData;
      exit;
   }

   public function getReportCustomAction() {
      $this->_helper->layout()->disableLayout();
      $this->_helper->viewRenderer->setNoRender();

      if ($this->_request->isXmlHttpRequest()) {
         $ordenDb = new Orden_Model_DbTable_Orden();

         $data = $this->_request->getParams();

         $originalFromDate = $data['from_date'];
         $newFromDate = date("Y-m-d", strtotime($originalFromDate));
         $originalToDate = $data['to_date'];
         $newToDate = date("Y-m-d", strtotime($originalToDate));

         if ($data['departament'] != 0) {
            $select = $ordenDb->select()->setIntegrityCheck(false)
                    ->from(array('od' => 'orden_departament'), array('id_departament'))->where('od.id_departament=?', $data['departament'])
                    ->join(array('o' => 'orden'), 'o.id = od.id_orden', array('nro_ordenes' => 'COUNT(*)'))
                    ->where('o.create_date > ?', $newFromDate)->where('o.create_date < ?', $newToDate)
                    ->join(array('d' => 'departament'), 'd.id = od.id_departament')
                    ->group('od.id_departament');
         } else {
            $select = $ordenDb->select()->setIntegrityCheck(false)
                    ->from(array('od' => 'orden_departament'), array('id_departament'))
                    ->join(array('o' => 'orden'), 'o.id = od.id_orden', array('nro_ordenes' => 'COUNT(*)'))
                    ->where('o.create_date > ?', $newFromDate)->where('o.create_date < ?', $newToDate)
                    ->join(array('d' => 'departament'), 'd.id = od.id_departament')
                    ->group('od.id_departament');
         }

         $dataOr = $ordenDb->fetchAll($select);
         $jsonData = Zend_Json::encode($dataOr->toArray());
         echo $jsonData;
         exit;
      }
   }

   public function reportJobAction() {
      $ordenDb = new Orden_Model_DbTable_Orden();
      $ordenJobDb = new Orden_Model_DbTable_OrdenJob();
      $tareaUser = new Tarea_Model_DbTable_TareaUser();
      $ordenDep = new Orden_Model_DbTable_OrdenDepartament();

      $request = $this->getRequest();
      $resultTotal = array();

      if ($request->isPost()) {

         $data = $request->getParams();
         $from_date = date("Y-m-d", strtotime($data['from_date']));
         $to_date = date("Y-m-d", strtotime($data['to_date']));
         $status = $data['status'];
         $departament = $data['departament'];

         if ($status == 9 && $departament == 0) {//no status and all agency
            $queryStatus = 'oj.status != 2 and oj.status != 5 and oj.status != 6';
            $queryAgency = 'od.id_departament=37 and od.id_departament=40 and od.id_departament=41';
         } else {
            if ($status != 9) {
               $queryStatus = 'oj.status=' . $status;
            } else {
               $queryStatus = 'oj.status != 2 and oj.status != 5 and oj.status != 6';
            }
            if ($departament != 0) {
               $queryAgency = 'od.id_departament=' . $departament;
            } else {
               $queryAgency = 'od.id_departament=37 || od.id_departament=40 || od.id_departament=41';
            }
         }

         $select = $ordenJobDb->select()->setIntegrityCheck(false)
                 ->from(array('oj' => 'orden_job'), array('*'))
                 ->join(array('o' => 'orden'), 'oj.id_orden = o.id', array('code', 'title', 'create_date'))
                 ->join(array('od' => 'orden_departament'), 'od.id_orden = o.id')
                 ->join(array('d' => 'departament'), 'd.id = od.id_departament', array('departament' => 'name'))
                 ->join(array('t' => 'tarea'), 't.id_job = oj.id', array('idtarea' => 'id'))
                 ->where($queryStatus)
                 ->where('o.create_date >= ?', $from_date)
                 ->where('oj.end_date_client <= ?', $to_date)
                 ->where($queryAgency);
         $result = $ordenJobDb->fetchAll($select);

         foreach ($result as $value) {
            $temp = array();
            $temp['code'] = $value->code;
            $temp['campania'] = $value->title;
            $temp['job'] = $value->name;
            $temp['create_date'] = $value->create_date;
            $temp['end_date'] = $value->end_date_client;
            $temp['status'] = $value->status;
            $temp['agency'] = $value->departament;

            //obtenemos los usuarios relacionadas con el trabajo
            $select = $tareaUser->select()->setIntegrityCheck(false)
                    ->from(array('tu' => 'tarea_user'), array('*'))
                    ->where('id_tarea=?', $value->idtarea)
                    ->join(array('u' => 'user'), 'u.id = tu.id_user');
            $resUser = $tareaUser->fetchAll($select);
            foreach ($resUser as $u)
               $nameUser .= $u->nombre . ' ' . $u->apellidos . ', ';
            $temp['users'] = $nameUser;

            $resultTotal[] = $temp;

            $nameUser = '';
         }
         $this->view->resultJob = $resultTotal;
      } else {
         // SOLO TAREAS CON SU ORDEN USUARIO PENDIENTES, CAMBIOS, EN PROGRESO
         $select = $ordenJobDb->select()->setIntegrityCheck(false)
                 ->from(array('oj' => 'orden_job'), array('*'))
                 ->join(array('o' => 'orden'), 'oj.id_orden = o.id', array('code', 'title', 'create_date'))
                 ->join(array('t' => 'tarea'), 't.id_job = oj.id', array('idtarea' => 'id'))
                 ->where('oj.status != 2 and oj.status != 5 and oj.status != 6'); //solo los pendientes, cambios, en progreso
         $result = $ordenJobDb->fetchAll($select);

         foreach ($result as $value) {
            $temp = array();
            $temp['code'] = $value->code;
            $temp['campania'] = $value->title;
            $temp['job'] = $value->name;
            $temp['create_date'] = $value->create_date;
            $temp['end_date'] = $value->end_date_client;
            $temp['status'] = $value->status;

            //Obtengo todos los departamentos involucrados en la orden
            $sel = $ordenDep->select()->setIntegrityCheck(false)
                    ->from(array('od' => 'orden_departament'), array('*'))->where('id_orden=?', $value->id_orden)
                    ->join(array('d' => 'departament'), 'd.id = od.id_departament');
            $resDep = $ordenDep->fetchAll($sel);
            foreach ($resDep as $d) {
               $nameDep .= $d->name . ', ';
            }
            $temp['agency'] = $nameDep;

            //obtenemos los usuarios relacionadas con el trabajo
            $select = $tareaUser->select()->setIntegrityCheck(false)
                    ->from(array('tu' => 'tarea_user'), array('*'))->where('id_tarea=?', $value->idtarea)
                    ->join(array('u' => 'user'), 'u.id = tu.id_user');
            $resUser = $tareaUser->fetchAll($select);
            foreach ($resUser as $u)
               $nameUser .= $u->nombre . ' ' . $u->apellidos . ', ';
            $temp['users'] = $nameUser;

            $resultTotal[] = $temp;

            $nameDep = '';
            $nameUser = '';
         }
         $this->view->resultJob = $resultTotal;
         // ***************************************************************************
         // SOLO TAREAS CON SU ORDEN USUARIO APROBADAS, PENDIENTE EN CLIENTE, ENTREGADAS
         // ***************************************************************************
         $resultTotalFinish = array();
         $select = $ordenJobDb->select()->setIntegrityCheck(false)
                 ->from(array('oj' => 'orden_job'), array('*'))
                 ->join(array('o' => 'orden'), 'oj.id_orden = o.id', array('code', 'title', 'create_date'))
                 ->join(array('t' => 'tarea'), 't.id_job = oj.id', array('idtarea' => 'id'))
                 ->where('oj.status = 2 || oj.status = 5 || oj.status = 6');
         $result = $ordenJobDb->fetchAll($select);

         foreach ($result as $value) {
            $temp = array();
            $temp['code'] = $value->code;
            $temp['campania'] = $value->title;
            $temp['job'] = $value->name;
            $temp['create_date'] = $value->create_date;
            $temp['end_date'] = $value->end_date_client;
            $temp['status'] = $value->status;

            //Obtengo todos los departamentos involucrados en la orden
            $sel = $ordenDep->select()->setIntegrityCheck(false)
                    ->from(array('od' => 'orden_departament'), array('*'))->where('id_orden=?', $value->id_orden)
                    ->join(array('d' => 'departament'), 'd.id = od.id_departament');
            $resDep = $ordenDep->fetchAll($sel);
            foreach ($resDep as $d) {
               $nameDep .= $d->name . ', ';
            }
            $temp['agency'] = $nameDep;

            //obtenemos los usuarios relacionadas con el trabajo
            $select = $tareaUser->select()->setIntegrityCheck(false)
                    ->from(array('tu' => 'tarea_user'), array('*'))->where('id_tarea=?', $value->idtarea)
                    ->join(array('u' => 'user'), 'u.id = tu.id_user');
            $resUser = $tareaUser->fetchAll($select);
            foreach ($resUser as $u)
               $nameUser .= $u->nombre . ' ' . $u->apellidos . ', ';
            $temp['users'] = $nameUser;

            $resultTotalFinish[] = $temp;

            $nameDep = '';
            $nameUser = '';
         }
         $this->view->resultJobFinish = $resultTotalFinish;
      }
   }

   public function reportJobUserAction() {
      $tareaUser = new Tarea_Model_DbTable_TareaUser();

      $select = $tareaUser->select()->setIntegrityCheck(false)
              ->from(array('u' => 'user'), array('*'))
              ->join(array('tu' => 'tarea_user'), 'u.id = tu.id_user', array('cantidad_trabajos' => 'COUNT(*)'))
              ->join(array('t' => 'tarea'), 't.id = tu.id_tarea')->where('t.status!=?', 5)//que omita las tareas aprobadas
              ->join(array('d' => 'departament'), 'd.id = u.client_departament', array('empresa' => 'd.name'))
              ->group('tu.id_user');

      $result = $tareaUser->fetchAll($select);

      $this->view->resultUserJob = $result;
   }

}

?>